IF OBJECT_ID('proc_StockWireropeKeepPlan') IS NOT NULL
    DROP PROCEDURE proc_StockWireropeKeepPlan;
GO
CREATE PROCEDURE proc_StockWireropeKeepPlan
AS
BEGIN
    /***** 在用绳维保定时任务用 *****/

    SET NOCOUNT ON;

    --定义表变量，用于保存今天要处理的哪些备绳需要维保
    DECLARE @tb_stock TABLE
    (
        id INT IDENTITY,
        stockID INT,
        groupInfoID INT,
        code VARCHAR(50),
        warehouseID INT,
        departmentID INT
    );

    --定义变量，用于保存今天要处理的数据条数
    DECLARE @cnt INT;



    DECLARE @now DATETIME = GETDATE(); --注释

    --获取未出库、维保周期不空、下次执行时间不空、下次执行时间距今天差7天的库存数据
    INSERT @tb_stock
    (
        stockID,
        groupInfoID,
        code,
        warehouseID,
        departmentID
    )
    SELECT StockID,
           GroupInfoID,
           Code,
           WarehouseID,
           DepartmentID
    FROM wb.Stock
    WHERE IsOut = 0
          AND KeepDay IS NOT NULL
          AND NextKeepDate IS NOT NULL
          AND DATEDIFF(DAY, @now, NextKeepDate) = 7;


    --如果今天要处理的数量为0，则不再执行
    SELECT @cnt = COUNT(1)
    FROM @tb_stock;

    IF @cnt = 0
    BEGIN
        RETURN;
    END;

    DECLARE @code_part1 VARCHAR(50)
        = 'BSWB' + RIGHT(CAST(DATEPART(YEAR, @now) AS VARCHAR), 2)
          + RIGHT('00' + CAST(DATEPART(MONTH, @now) AS VARCHAR), 2); --定义编码的前缀

    DECLARE @code_part2 INT = 0; --定义编码的数字位

    DECLARE @code_full VARCHAR(50); --定义完整编码

    --如果存在前缀的数据，则取出最后一条的编码的数字部分
    IF EXISTS
    (
        SELECT *
        FROM wb.StockWireropeKeep
        WHERE Code LIKE @code_part1 + '%'
    )
    BEGIN
        DECLARE @db_code VARCHAR(50);

        SELECT TOP 1
               @db_code = Code
        FROM wb.StockWireropeKeep
        WHERE Code LIKE @code_part1 + '%'
        ORDER BY Code DESC;

        SET @code_part2 = CAST(RIGHT(@db_code, 4) AS INT);
    END;


    DECLARE @i INT = 1; --注释

    DECLARE @stockID INT; --注释
    DECLARE @groupInfoID INT; --注释
    DECLARE @code VARCHAR(50); --注释
    DECLARE @warehouseID INT; --注释
    DECLARE @departmentID INT; --注释，这个暂时没有用，只是先给取出来


    BEGIN TRANSACTION;
    BEGIN TRY

        WHILE @i <= @cnt
        BEGIN
            SELECT @stockID = stockID,
                   @groupInfoID = groupInfoID,
                   @code = code,
                   @warehouseID = warehouseID,
                   @departmentID = departmentID
            FROM @tb_stock
            WHERE id = @i;

            SET @code_full = @code_part1 + RIGHT('0000' + CAST(@code_part2 + @i AS VARCHAR), 4);

            --添加备绳维保记录
            INSERT wb.StockWireropeKeep
            (
                GroupInfoID,
                Code,
                StockID,
                Status,
                CreateUserID,
                CreateUserName,
                CreateDate
            )
            VALUES
            (   @groupInfoID, -- GroupInfoID - int
                @code_full,   -- Code - varchar(50)
                @stockID,     -- StockID - int
                0,            -- Status - int
                0,            -- CreateUserID - int
                N'系统',        -- CreateUserName - nvarchar(50)
                @now          -- CreateDate - datetime
                );


            --重新计算下次维保日期
            UPDATE wb.Stock
            SET NextKeepDate = DATEADD(DAY, KeepDay, NextKeepDate)
            WHERE StockID = @stockID;


            --发通知
            INSERT rec.Message
            (
                GroupInfoID,
                Type,
                Title,
                ReceiveUserID,
                Data,
                Status,
                CreateDate
            )
            SELECT @groupInfoID,
                   1,
                   '备绳' + @code_full + '检测',
                   T.UserID,
                   '{"code":"' + @code_full + '"}',
                   0,
                   @now
            FROM
            (
                --查询当前仓库的所有管理员
                SELECT UserID
                FROM base.WarehouseUser
                WHERE GroupInfoID = @groupInfoID
                      AND WarehouseID = @warehouseID
                UNION ALL

                --查询有备用绳检测模块30501的所有用户
                SELECT ur.UserID
                FROM dbo.UserRole ur
                    INNER JOIN dbo.RolePermission rp
                        ON rp.RoleID = ur.RoleID
                    INNER JOIN dbo.Permission p
                        ON p.PermissionID = rp.PermissionID
                WHERE p.PerID = 30501
                      AND ur.GroupInfoID = @groupInfoID
            ) AS T --通过union all + group by ，获取这2个集合的交集
            GROUP BY T.UserID
            HAVING COUNT(T.UserID) > 1;

            SET @i += 1;
        END;

        COMMIT TRANSACTION;
    END TRY
    BEGIN CATCH
        PRINT '错误';
        ROLLBACK TRANSACTION;
    END CATCH;

    SET NOCOUNT OFF;
END;
GO
